USE [ANTS_NetServer]
GO

/****** Object:  StoredProcedure [dbo].[Proc_GetRecordByPage]    Script Date: 04/21/2013 15:26:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO









--创建分页的存储过程

CREATE          PROCEDURE [dbo].[Proc_GetRecordByPage] --'v_EmailInfoRecive','PK_EmailReceive','*',10,2,0,0,' PK_Employee=1' --and (SendDate<=''2007-4-14 13:20:05'' and SendDate>=''2007-3-14 13:20:04'') and IsRead=''未读'' '
    @tblName      varchar(255),       -- 表名
    @fldName      varchar(255),       -- 主键字段名
    @DisName      varchar(255),       --显示列字符串 * 或 PK_ID,Name
    @PageSize     int,           -- 页尺寸
    @PageIndex    int,            -- 页码
    @IsReCount    bit,            -- 返回记录总数, 非 0 值则返回
    @OrderType    bit,            -- 设置排序类型, 非 0 值则降序
    @strWhere     varchar(1000)   -- 查询条件 (注意: 不要加 where,可加分组 Group by)
AS

declare @strSQL   varchar(6000)       -- 主语句
declare @strTmp   varchar(4000)        -- 临时变量
declare @strOrder varchar(4000)        -- 排序类型
	if @OrderType != 0
	begin
	    set @strTmp = '<(select min'
	    set @strOrder = ' order by [' + @fldName +'] desc'
	end
	else
	begin
	    set @strTmp = '>(select max'
	    set @strOrder = ' order by [' + @fldName +'] asc'
	end


set @strSQL = 'select top ' + str(@PageSize) + ' '+@DisName+' from ['
    + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
    + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
    + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
    + @strOrder

if @strWhere !=' '
    set @strSQL = 'select top ' + str(@PageSize) + '  '+@DisName+' from  ['
        + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
        + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
        + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
        + @strOrder + ') as tblTmp) and ' + @strWhere +' '+@strOrder

if @PageIndex = 1
begin
    set @strTmp =''
    if @strWhere != ' '
        set @strTmp = ' where ' + @strWhere

    set @strSQL = 'select top ' + str(@PageSize) + '  '+@DisName+' from  ['
        + @tblName + ']' + @strTmp + ' ' + @strOrder
end

if @IsReCount ! = 0
	Begin
		if @strWhere !=''
			set @strSQL = 'select count(*) as Total  from ( select '+@DisName+'  from [' + @tblName + ']'+' where ' + @strWhere+') as tmptable'
		else
			set @strSQL = 'select count(*) as Total from  ( select '+@DisName+'  from [' + @tblName + '] ) as tmptable'
	end
    
--print @strSQL
exec (@strSQL)






GO

